/**
 * 
 */
package metier.JDBC;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;

import metier.AnneeEtude;
import metier.ElementListe;
import metier.ExceptionMetier;
import metier.Formation;

/**
 * @author Vincent AMODEOS
 * 
 */

public class AnneeEtudeJDBC extends AnneeEtude {

	// Attributs
	

	/* Attributs prives */
	//Requete SQL permettant de mettre a jour l'annee d'etude dans la base de donnees 
	private final static String updateQuery = "UPDATE AnneeEtude SET date_debut_annee = ?,date_fin_annee = ?,nom_anneeetude = ?,description_anneeetude = ?, nb_seances_anneeetude_matin = ?, nb_seances_anneeetude_aprem = ?, duree_seance = ? , formation = ? where id_anneeetude = ?";
	//Requete SQL permettant de recuperer la liste de toutes les annees d'etude de la base de donnees
	private final static String selectQuery = "SELECT * FROM anneeetude";
	//Requete SQL permettant de recuperer les informations de l'annee d'etude dans la base de donnees
	private final static String loadQuery = "SELECT * FROM anneeetude WHERE id_anneeetude = ?";
	//Requete SQL permettant de supprimer les informations de la formation dans la base de donnees
	private final static String deleteQuery="DELETE FROM anneeetude WHERE id_anneeetude= ?";
	//Requete SQL permettant d'inserer les informations de la formation dans la base de donnees
	private final static String insertQuery="INSERT INTO anneeetude VALUES (?,?,?,?,?,?,?,?,?)";
	//Requete SQL permettant d'obtenir un nouve identifiant pour l'annee d'etude
	private final static String idQuery="Select seq_anneeetude.nextval FROM dual";
	
	private int idAnnee = 0;
	
	/**
	 * @param nom
	 * @param dateDebut
	 * @param dateFin
	 * @param description
	 * @param nbSeancesMatin
	 * @param nbSeancesAprem
	 * @param dureeSeance
	 */
	public AnneeEtudeJDBC(String nom, Date dateDebut, Date dateFin,String description, int nbSeancesMatin, int nbSeancesAprem,	int dureeSeance)
	{
		super(nom, dateDebut, dateFin, description, nbSeancesMatin, nbSeancesAprem,dureeSeance);
	
	}

        public AnneeEtudeJDBC(){
            super();
        }

	
	/**
	 * @param idannee l'idannee a modifier
	 */
	public void setIdAnnee(int idannee) {
		this.idAnnee = idannee;
	}
	
	/**
	 * @return l'identifiant de l'annee d'etude
	 */public int getIdAnnee() {return idAnnee;}
	 
	 protected int createId() throws ExceptionMetier {
	        try {
	            Connection con = ConnectionJDBC.getConnection();
	            Statement sta = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
	            ResultSet rset = sta.executeQuery(idQuery);
	            if (!rset.next()) {
	                throw new ExceptionMetier("creating new id failed due to non generated id");
	            }
	            int newId = rset.getInt(1);
	            rset.close();
	            sta.close();
	            return newId;
	        } catch (ClassNotFoundException ex) {
	            throw new ExceptionMetier("creating new id failed", ex);
	        } catch (SQLException ex) {
	            throw new ExceptionMetier("creating new id failed", ex);
	        }
	    }
	 
	 
	 public void delete() throws ExceptionMetier {
			try {
				Connection con = ConnectionJDBC.getConnection();
				PreparedStatement sta = con.prepareStatement(deleteQuery);
				//R�cup�re toutes les seances existantes dans la base de donn�es
		        sta.setInt(1,this.getIdAnnee());
		        sta.executeUpdate();
			}
			catch (ClassNotFoundException ex) {throw new ExceptionMetier("Delete failed", ex);} 
			catch (SQLException ex) {throw new ExceptionMetier("Delete failed", ex);}
		}

	/* (non-Javadoc)
	 * @see metier.Seance#insert()
	 */
	@Override
	public void insert() throws ExceptionMetier {
		try {
			Connection con = ConnectionJDBC.getConnection();
            PreparedStatement sta = con.prepareStatement(insertQuery);
            setIdAnnee(createId());
            sta.setInt(1, getIdAnnee());
            sta.setDate(2, new java.sql.Date(getDateDebut().getTime()));
            sta.setDate(3, new java.sql.Date(getDateFin().getTime()));
            sta.setString(4, getNom());
            sta.setString(5, getDescription());
            sta.setInt(6, this.getNbSeancesMatin());
            sta.setInt(7, this.getNbSeancesAprem());
            sta.setInt(8, this.getDureeSeance());
            sta.setString(9, this.getFormation().getNom());
            sta.executeUpdate();
           
        } 
		catch (ClassNotFoundException ex) {throw new ExceptionMetier("Insert failed", ex);} 
        catch (SQLException ex) {throw new ExceptionMetier("Insert failed", ex);}
	}

	/* (non-Javadoc)
	 * @see metier.Seance#update()
	 */
	@Override
	public void update() throws ExceptionMetier {
		try {
            Connection con = ConnectionJDBC.getConnection();
            PreparedStatement updateAnnee = con.prepareStatement(updateQuery);
            updateAnnee.setDate(1, new java.sql.Date(this.getDateDebut().getTime()));
            updateAnnee.setDate(2, new java.sql.Date(this.getDateFin().getTime()));
            updateAnnee.setString(3, this.getNom());
            updateAnnee.setString(4, this.getDescription());
            updateAnnee.setInt(5, this.getNbSeancesMatin());
            updateAnnee.setInt(6, this.getNbSeancesAprem());
            updateAnnee.setInt(7, this.getDureeSeance());
            updateAnnee.setInt(9, this.getIdAnnee());
            updateAnnee.setString(8, this.getFormation().getNom());
            updateAnnee.executeUpdate();
        } 
		catch (ClassNotFoundException ex) {throw new ExceptionMetier("Update failed", ex);} 
		catch (SQLException ex) {throw new ExceptionMetier("Update failed", ex);}
	}
	
	@Override
	/* (non-Javadoc)
	 * @see metier.Seance#load(int idSeance)
	 */

	public void load(int id) throws ExceptionMetier {
		try {
            Connection con = ConnectionJDBC.getConnection();
            PreparedStatement loadSeance = con.prepareStatement(loadQuery);
            loadSeance.setInt(1, id);
            ResultSet res = loadSeance.executeQuery();
            if (!res.next()) {
                throw new ExceptionMetier("Load failed due to non existing Object");
            }
            if (res.getInt("ID_ANNEEETUDE") != id) {
                throw new ExceptionMetier("Load failed due to non existing Object");
            }
            this.setIdAnnee(id);
            this.setDateDebut(res.getDate("date_debut_Annee"));
            this.setDateFin(res.getDate("date_fin_Annee"));
            this.setNom(res.getString("nom_anneeetude"));
            this.setDescription(res.getString("description_Anneeetude"));
            this.setNbSeancesMatin(res.getInt("nb_seances_anneeetude_matin"));
            this.setNbSeancesAprem(res.getInt("nb_seances_anneeetude_aprem"));
            this.setDureeSeance(res.getInt("duree_seance"));
            Formation form = new FormationJDBC();
            form.load(res.getString("formation"));
            this.setFormation(form);
            
        } 
		catch (ClassNotFoundException ex) {throw new ExceptionMetier("Load failed", ex);} 
        catch (SQLException ex) {throw new ExceptionMetier("Load failed", ex);}
	}
	
	public static ArrayList <ElementListe> select() throws ExceptionMetier {
		ArrayList <ElementListe> listeSalle = new ArrayList<ElementListe>();
		try {
		    ResultSet res = null;
		    Connection con = ConnectionJDBC.getConnection();
		    Statement s = con.createStatement();
		    res = s.executeQuery(selectQuery);
			// Ajoute les salles une par une a la liste
			while (res.next())
			{		
				listeSalle.add(new ElementListe(res.getInt("id_anneeetude"),res.getString("nom_anneeetude")));
			}
		    return listeSalle;
		} 
		catch (ClassNotFoundException ex) {throw new ExceptionMetier("Select failed", ex);} 
		catch (SQLException ex) {throw new ExceptionMetier("Select failed", ex);}
	}
	
}
